"""
信访记录管理API接口
"""
from fastapi import APIRouter, Depends, HTTPException, Query, UploadFile, File
from sqlalchemy.orm import Session
from sqlalchemy import func, desc, and_, or_
from pydantic import BaseModel, Field
from typing import Dict, Any, List, Optional
from datetime import datetime, date
import pandas as pd
import json

from core.database import get_db
from core.logging_config import get_logger, log_api_request
from core.auth import get_current_active_user, require_permission
from models.petition_record import PetitionRecord
from models.user import User
from models.analysis_task import AnalysisTask

# 路由器
petition_records_router = APIRouter(tags=["信访记录管理"])

# 日志记录器
logger = get_logger("petition_records_api")


class PetitionRecordCreate(BaseModel):
    """创建信访记录请求模型"""
    xh: str = Field(..., description="信访记录id")
    ccjbz: Optional[str] = Field(None, description="初重件标志")
    flnr: Optional[str] = Field(None, description="分类内容")
    tsr: Optional[str] = Field(None, description="姓名")
    xfnr: Optional[str] = Field(None, description="投诉内容")
    ts_gk: Optional[str] = Field(None, description="概况")
    ts_xzq: Optional[str] = Field(None, description="问题属地")
    ts_ds: Optional[str] = Field(None, description="地市")
    ts_qxs: Optional[str] = Field(None, description="区县市")
    ts_jd: Optional[str] = Field(None, description="乡镇街道")
    ts_dz: Optional[str] = Field(None, description="地址")
    ts_qxdw: Optional[str] = Field(None, description="去向单位")
    ts_dfnr: Optional[str] = Field(None, description="答复内容")
    djsj: Optional[datetime] = Field(None, description="登记时间")
    ts_bz: Optional[str] = Field(None, description="备注")
    data_source_id: Optional[int] = Field(None, description="数据源ID")


class PetitionRecordUpdate(BaseModel):
    """更新信访记录请求模型"""
    ccjbz: Optional[str] = Field(None, description="初重件标志")
    flnr: Optional[str] = Field(None, description="分类内容")
    tsr: Optional[str] = Field(None, description="姓名")
    xfnr: Optional[str] = Field(None, description="投诉内容")
    ts_gk: Optional[str] = Field(None, description="概况")
    ts_xzq: Optional[str] = Field(None, description="问题属地")
    ts_ds: Optional[str] = Field(None, description="地市")
    ts_qxs: Optional[str] = Field(None, description="区县市")
    ts_jd: Optional[str] = Field(None, description="乡镇街道")
    ts_dz: Optional[str] = Field(None, description="地址")
    ts_qxdw: Optional[str] = Field(None, description="去向单位")
    ts_dfnr: Optional[str] = Field(None, description="答复内容")
    djsj: Optional[datetime] = Field(None, description="登记时间")
    ts_bz: Optional[str] = Field(None, description="备注")
    data_source_id: Optional[int] = Field(None, description="数据源ID")


class PetitionRecordFilter(BaseModel):
    """信访记录过滤器"""
    keyword: Optional[str] = Field(None, description="关键词搜索")
    ccjbz: Optional[str] = Field(None, description="初重件标志")
    flnr: Optional[str] = Field(None, description="分类内容")
    tsr: Optional[str] = Field(None, description="姓名")
    ts_ds: Optional[str] = Field(None, description="地市")
    ts_qxs: Optional[str] = Field(None, description="区县市")
    ts_xzq: Optional[str] = Field(None, description="问题属地")
    ts_qxdw: Optional[str] = Field(None, description="去向单位")
    start_date: Optional[date] = Field(None, description="开始日期")
    end_date: Optional[date] = Field(None, description="结束日期")
    data_source_id: Optional[int] = Field(None, description="数据源ID")


@petition_records_router.get("/")
@log_api_request("list_petition_records")
@require_permission("petition_record:view")
async def list_petition_records(
    keyword: Optional[str] = Query(None, description="关键词搜索"),
    ccjbz: Optional[str] = Query(None, description="初重件标志"),
    flnr: Optional[str] = Query(None, description="分类内容"),
    tsr: Optional[str] = Query(None, description="姓名"),
    ts_ds: Optional[str] = Query(None, description="地市"),
    ts_qxs: Optional[str] = Query(None, description="区县市"),
    ts_xzq: Optional[str] = Query(None, description="问题属地"),
    ts_qxdw: Optional[str] = Query(None, description="去向单位"),
    start_date: Optional[date] = Query(None, description="开始日期"),
    end_date: Optional[date] = Query(None, description="结束日期"),
    data_source_id: Optional[int] = Query(None, description="数据源ID"),
    page: int = Query(1, ge=1, description="页码"),
    page_size: int = Query(20, ge=1, le=100, description="每页数量"),
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """获取信访记录列表"""
    logger.info("获取信访记录列表请求")
    
    try:
        # 构建查询
        query = db.query(PetitionRecord).filter(PetitionRecord.is_deleted == False)
        
        # 应用过滤器
        if keyword:
            query = query.filter(
                or_(
                    PetitionRecord.xh.contains(keyword),
                    PetitionRecord.tsr.contains(keyword),
                    PetitionRecord.xfnr.contains(keyword),
                    PetitionRecord.ts_gk.contains(keyword),
                    PetitionRecord.ts_dz.contains(keyword)
                )
            )
        
        if ccjbz:
            query = query.filter(PetitionRecord.ccjbz == ccjbz)
        if flnr:
            query = query.filter(PetitionRecord.flnr == flnr)
        if tsr:
            query = query.filter(PetitionRecord.tsr.contains(tsr))
        if ts_ds:
            query = query.filter(PetitionRecord.ts_ds == ts_ds)
        if ts_qxs:
            query = query.filter(PetitionRecord.ts_qxs == ts_qxs)
        if ts_xzq:
            query = query.filter(PetitionRecord.ts_xzq == ts_xzq)
        if ts_qxdw:
            query = query.filter(PetitionRecord.ts_qxdw == ts_qxdw)
        if data_source_id:
            query = query.filter(PetitionRecord.data_source_id == data_source_id)
        
        if start_date:
            query = query.filter(PetitionRecord.djsj >= start_date)
        if end_date:
            query = query.filter(PetitionRecord.djsj <= end_date)
        
        # 计算总数
        total = query.count()
        
        # 分页查询
        records = query.order_by(desc(PetitionRecord.created_at)).offset(
            (page - 1) * page_size
        ).limit(page_size).all()
        
        # 转换为响应格式
        record_list = [record.to_dict() for record in records]
        
        return {
            "total": total,
            "page": page,
            "page_size": page_size,
            "items": record_list,
            "total_pages": (total + page_size - 1) // page_size
        }
        
    except Exception as e:
        logger.error(f"获取信访记录列表失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.post("/")
@log_api_request("create_petition_record")
@require_permission("petition_record:create")
async def create_petition_record(
    record: PetitionRecordCreate,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """创建信访记录"""
    logger.info("创建信访记录请求")
    
    try:
        # 检查记录ID是否已存在
        existing_record = db.query(PetitionRecord).filter(
            PetitionRecord.xh == record.xh,
            PetitionRecord.is_deleted == False
        ).first()
        
        if existing_record:
            raise HTTPException(status_code=400, detail="信访记录ID已存在")
        
        # 创建新记录
        new_record = PetitionRecord(
            **record.dict(),
            creator_id=current_user.id
        )
        
        db.add(new_record)
        db.commit()
        db.refresh(new_record)
        
        logger.info(f"成功创建信访记录: {new_record.xh}")
        return new_record.to_dict()
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"创建信访记录失败: {str(e)}")
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.get("/{record_id}")
@log_api_request("get_petition_record")
@require_permission("petition_record:view")
async def get_petition_record(
    record_id: str,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """获取信访记录详情"""
    logger.info(f"获取信访记录详情请求: {record_id}")
    
    try:
        record = db.query(PetitionRecord).filter(
            PetitionRecord.xh == record_id,
            PetitionRecord.is_deleted == False
        ).first()
        
        if not record:
            raise HTTPException(status_code=404, detail="信访记录不存在")
        
        return record.to_dict()
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取信访记录详情失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.put("/{record_id}")
@log_api_request("update_petition_record")
@require_permission("petition_record:update")
async def update_petition_record(
    record_id: str,
    record_update: PetitionRecordUpdate,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """更新信访记录"""
    logger.info(f"更新信访记录请求: {record_id}")
    
    try:
        record = db.query(PetitionRecord).filter(
            PetitionRecord.xh == record_id,
            PetitionRecord.is_deleted == False
        ).first()
        
        if not record:
            raise HTTPException(status_code=404, detail="信访记录不存在")
        
        # 更新记录
        update_data = record_update.dict(exclude_unset=True)
        for field, value in update_data.items():
            setattr(record, field, value)
        
        db.commit()
        db.refresh(record)
        
        logger.info(f"成功更新信访记录: {record.xh}")
        return record.to_dict()
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"更新信访记录失败: {str(e)}")
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.delete("/{record_id}")
@log_api_request("delete_petition_record")
@require_permission("petition_record:delete")
async def delete_petition_record(
    record_id: str,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """删除信访记录"""
    logger.info(f"删除信访记录请求: {record_id}")
    
    try:
        record = db.query(PetitionRecord).filter(
            PetitionRecord.xh == record_id,
            PetitionRecord.is_deleted == False
        ).first()
        
        if not record:
            raise HTTPException(status_code=404, detail="信访记录不存在")
        
        # 软删除
        record.soft_delete()
        db.commit()
        
        logger.info(f"成功删除信访记录: {record.xh}")
        return {"message": "信访记录删除成功"}
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"删除信访记录失败: {str(e)}")
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.post("/batch")
@log_api_request("batch_create_petition_records")
@require_permission("petition_record:create")
async def batch_create_petition_records(
    records: List[PetitionRecordCreate],
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """批量创建信访记录"""
    logger.info(f"批量创建信访记录请求，数量: {len(records)}")
    
    try:
        created_records = []
        failed_records = []
        
        for record_data in records:
            try:
                # 检查记录ID是否已存在
                existing_record = db.query(PetitionRecord).filter(
                    PetitionRecord.xh == record_data.xh,
                    PetitionRecord.is_deleted == False
                ).first()
                
                if existing_record:
                    failed_records.append({
                        "xh": record_data.xh,
                        "reason": "记录ID已存在"
                    })
                    continue
                
                # 创建新记录
                new_record = PetitionRecord(
                    **record_data.dict(),
                    creator_id=current_user.id
                )
                
                db.add(new_record)
                created_records.append(new_record)
                
            except Exception as e:
                failed_records.append({
                    "xh": record_data.xh,
                    "reason": str(e)
                })
        
        db.commit()
        
        # 刷新所有创建的记录
        for record in created_records:
            db.refresh(record)
        
        return {
            "message": f"批量创建完成，成功: {len(created_records)}，失败: {len(failed_records)}",
            "created_count": len(created_records),
            "failed_count": len(failed_records),
            "created_records": [record.to_dict() for record in created_records],
            "failed_records": failed_records
        }
        
    except Exception as e:
        logger.error(f"批量创建信访记录失败: {str(e)}")
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.post("/import")
@log_api_request("import_petition_records")
@require_permission("petition_record:create")
async def import_petition_records(
    file: UploadFile = File(...),
    data_source_id: Optional[int] = None,
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """导入信访记录"""
    logger.info(f"导入信访记录请求，文件: {file.filename}")
    
    try:
        # 检查文件类型
        if not file.filename.endswith(('.xlsx', '.xls', '.csv')):
            raise HTTPException(status_code=400, detail="只支持Excel和CSV文件")
        
        # 读取文件内容
        contents = await file.read()
        
        if file.filename.endswith('.csv'):
            df = pd.read_csv(contents)
        else:
            df = pd.read_excel(contents)
        
        # 验证必需列
        required_columns = ['xh']  # 至少需要信访记录ID
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            raise HTTPException(status_code=400, detail=f"缺少必需列: {missing_columns}")
        
        # 转换数据格式
        records = []
        for _, row in df.iterrows():
            record_data = {
                'xh': str(row.get('xh', '')),
                'ccjbz': str(row.get('ccjbz', '')) if pd.notna(row.get('ccjbz')) else None,
                'flnr': str(row.get('flnr', '')) if pd.notna(row.get('flnr')) else None,
                'tsr': str(row.get('tsr', '')) if pd.notna(row.get('tsr')) else None,
                'xfnr': str(row.get('xfnr', '')) if pd.notna(row.get('xfnr')) else None,
                'ts_gk': str(row.get('ts_gk', '')) if pd.notna(row.get('ts_gk')) else None,
                'ts_xzq': str(row.get('ts_xzq', '')) if pd.notna(row.get('ts_xzq')) else None,
                'ts_ds': str(row.get('ts_ds', '')) if pd.notna(row.get('ts_ds')) else None,
                'ts_qxs': str(row.get('ts_qxs', '')) if pd.notna(row.get('ts_qxs')) else None,
                'ts_jd': str(row.get('ts_jd', '')) if pd.notna(row.get('ts_jd')) else None,
                'ts_dz': str(row.get('ts_dz', '')) if pd.notna(row.get('ts_dz')) else None,
                'ts_qxdw': str(row.get('ts_qxdw', '')) if pd.notna(row.get('ts_qxdw')) else None,
                'ts_dfnr': str(row.get('ts_dfnr', '')) if pd.notna(row.get('ts_dfnr')) else None,
                'ts_bz': str(row.get('ts_bz', '')) if pd.notna(row.get('ts_bz')) else None,
                'data_source_id': data_source_id
            }
            
            # 处理登记时间
            if pd.notna(row.get('djsj')):
                try:
                    record_data['djsj'] = pd.to_datetime(row['djsj'])
                except:
                    pass
            
            records.append(PetitionRecordCreate(**record_data))
        
        # 批量创建
        result = await batch_create_petition_records(records, current_user, db)
        
        logger.info(f"成功导入信访记录: {result['created_count']} 条")
        return result
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"导入信访记录失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.get("/statistics/summary")
@log_api_request("petition_records_summary")
@require_permission("petition_record:view")
async def get_petition_records_summary(
    start_date: Optional[date] = Query(None, description="开始日期"),
    end_date: Optional[date] = Query(None, description="结束日期"),
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """获取信访记录统计摘要"""
    logger.info("获取信访记录统计摘要请求")
    
    try:
        query = db.query(PetitionRecord).filter(PetitionRecord.is_deleted == False)
        
        if start_date:
            query = query.filter(PetitionRecord.djsj >= start_date)
        if end_date:
            query = query.filter(PetitionRecord.djsj <= end_date)
        
        # 基础统计
        total_records = query.count()
        
        # 按初重件标志统计
        ccjbz_stats = db.query(
            PetitionRecord.ccjbz,
            func.count(PetitionRecord.id).label('count')
        ).filter(
            PetitionRecord.is_deleted == False
        ).filter(
            PetitionRecord.djsj >= start_date if start_date else True,
            PetitionRecord.djsj <= end_date if end_date else True
        ).group_by(PetitionRecord.ccjbz).all()
        
        # 按地市统计
        ds_stats = db.query(
            PetitionRecord.ts_ds,
            func.count(PetitionRecord.id).label('count')
        ).filter(
            PetitionRecord.is_deleted == False,
            PetitionRecord.ts_ds.isnot(None)
        ).filter(
            PetitionRecord.djsj >= start_date if start_date else True,
            PetitionRecord.djsj <= end_date if end_date else True
        ).group_by(PetitionRecord.ts_ds).all()
        
        # 按分类统计
        flnr_stats = db.query(
            PetitionRecord.flnr,
            func.count(PetitionRecord.id).label('count')
        ).filter(
            PetitionRecord.is_deleted == False,
            PetitionRecord.flnr.isnot(None)
        ).filter(
            PetitionRecord.djsj >= start_date if start_date else True,
            PetitionRecord.djsj <= end_date if end_date else True
        ).group_by(PetitionRecord.flnr).all()
        
        # 今日统计
        today = date.today()
        today_records = query.filter(
            func.date(PetitionRecord.djsj) == today
        ).count()
        
        return {
            "total_records": total_records,
            "today_records": today_records,
            "ccjbz_distribution": [
                {"ccjbz": stat.ccjbz or "未知", "count": stat.count}
                for stat in ccjbz_stats
            ],
            "region_distribution": [
                {"region": stat.ts_ds or "未知", "count": stat.count}
                for stat in ds_stats
            ],
            "category_distribution": [
                {"category": stat.flnr or "未分类", "count": stat.count}
                for stat in flnr_stats
            ]
        }
        
    except Exception as e:
        logger.error(f"获取信访记录统计摘要失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))


@petition_records_router.get("/export/{format}")
@log_api_request("export_petition_records")
@require_permission("petition_record:export")
async def export_petition_records(
    format: str,
    keyword: Optional[str] = Query(None, description="关键词搜索"),
    ccjbz: Optional[str] = Query(None, description="初重件标志"),
    flnr: Optional[str] = Query(None, description="分类内容"),
    ts_ds: Optional[str] = Query(None, description="地市"),
    start_date: Optional[date] = Query(None, description="开始日期"),
    end_date: Optional[date] = Query(None, description="结束日期"),
    current_user: User = Depends(get_current_active_user),
    db: Session = Depends(get_db)
):
    """导出信访记录"""
    logger.info(f"导出信访记录请求，格式: {format}")
    
    try:
        # 构建查询
        query = db.query(PetitionRecord).filter(PetitionRecord.is_deleted == False)
        
        if keyword:
            query = query.filter(
                or_(
                    PetitionRecord.xh.contains(keyword),
                    PetitionRecord.tsr.contains(keyword),
                    PetitionRecord.xfnr.contains(keyword)
                )
            )
        
        if ccjbz:
            query = query.filter(PetitionRecord.ccjbz == ccjbz)
        if flnr:
            query = query.filter(PetitionRecord.flnr == flnr)
        if ts_ds:
            query = query.filter(PetitionRecord.ts_ds == ts_ds)
        if start_date:
            query = query.filter(PetitionRecord.djsj >= start_date)
        if end_date:
            query = query.filter(PetitionRecord.djsj <= end_date)
        
        records = query.all()
        
        if format == "json":
            export_data = {
                "export_info": {
                    "format": "json",
                    "exported_at": datetime.now().isoformat(),
                    "total_count": len(records),
                    "exported_by": current_user.username
                },
                "records": [record.to_dict() for record in records]
            }
            
            return export_data
        
        elif format == "csv":
            # 创建DataFrame
            data = []
            for record in records:
                data.append({
                    "信访记录ID": record.xh,
                    "初重件标志": record.ccjbz,
                    "分类内容": record.flnr,
                    "姓名": record.tsr,
                    "投诉内容": record.xfnr,
                    "概况": record.ts_gk,
                    "问题属地": record.ts_xzq,
                    "地市": record.ts_ds,
                    "区县市": record.ts_qxs,
                    "乡镇街道": record.ts_jd,
                    "地址": record.ts_dz,
                    "去向单位": record.ts_qxdw,
                    "答复内容": record.ts_dfnr,
                    "登记时间": record.get_registration_time_display(),
                    "备注": record.ts_bz,
                    "创建时间": record.created_at.strftime("%Y-%m-%d %H:%M:%S")
                })
            
            df = pd.DataFrame(data)
            
            # 返回CSV数据
            return {
                "filename": f"petition_records_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv",
                "data": df.to_csv(index=False, encoding='utf-8-sig')
            }
        
        else:
            raise HTTPException(status_code=400, detail="不支持的导出格式")
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"导出信访记录失败: {str(e)}")
        raise HTTPException(status_code=500, detail=str(e))